Step 1: Problem Statement Analysis — LinkedIn

Problem Statement

"Design a Data Model for a Professional Networking Platform like LinkedIn"

Understanding the Problem

What is LinkedIn?

LinkedIn is a professional networking platform with 1 billion+ members globally. Users:

• Create detailed professional profiles (resume-like structure)
• Build professional networks through connection requests (bidirectional)
• Follow thought leaders and companies (unidirectional)
• Share posts, articles, and professional updates in a news feed
• Search and apply for jobs through the Jobs marketplace
• Send direct messages (InMail for non-connections)
• Give and receive skill endorsements and written recommendations
• Create and manage Company Pages with employee associations
• Join professional groups and participate in discussions
• Earn certifications and showcase projects, publications, and patents
• Receive notifications for profile views, connection requests, job alerts, and engagement

Core Requirements Analysis

1. Professional Profile Management

What we need:

• Rich profile: headline, summary, profile photo, background photo
• Work experience history (multiple positions, with company association)
• Education history (multiple institutions, degrees)
• Skills list with endorsement counts
• Certifications and licenses
• Projects, publications, patents, volunteer experience
• Contact info, location, industry
• Profile visibility settings (public, connections-only, private)
• Custom profile URL (vanity URL like /in/john-doe)

Why it matters:

• The profile IS the product — it's a living resume
• Recruiters search profiles using faceted filters (skills, location, company, title)
• Profile completeness drives engagement (LinkedIn shows a "profile strength" meter)

2. Connection Network (Social Graph)

What we need:

• Connection requests: send, accept, reject, withdraw
• Bidirectional connections (mutual, like Facebook friends)
• Connection degree tracking: 1st (direct), 2nd (friend-of-friend), 3rd
• Follow without connecting (unidirectional, like Twitter)
• Block and report users
• Connection limit: max 30,000 connections per user
• "People You May Know" recommendations based on mutual connections, company, school

Why it matters:

• The connection graph powers everything: feed ranking, job recommendations, messaging access
• 2nd/3rd degree visibility is a core business feature (drives Premium subscriptions)
• Mutual connection count is shown on every profile and invitation

3. News Feed & Content

What we need:

• Post types: text, image, video, document (carousel), poll, article, event, newsletter
• Reactions: Like, Celebrate, Support, Love, Insightful, Funny
• Comments with nested replies (threaded)
• Reposts (share with or without comment)
• Mentions (@user) and hashtags (#topic)
• Content visibility: Public, Connections Only, Group Members
• Post analytics: impressions, unique views, engagement rate

Why it matters:

• The feed is the primary engagement surface
• Content virality through reactions, comments, and reposts drives DAU
• Post analytics attract content creators and thought leaders

4. Company Pages & Employee Association

What we need:

• Company profiles: name, logo, industry, size, headquarters, description
• Multiple page admins with different roles (Super Admin, Content Admin, Analyst)
• Employee association (auto-linked from work experience)
• Company followers (unidirectional)
• Company posts and sponsored content
• Company analytics: follower demographics, post performance
• Showcase Pages (sub-pages for products or initiatives)

Why it matters:

• Companies are the demand side for jobs and ads
• Employee count and follower count are trust signals
• Company pages are the entry point for employer branding

5. Jobs Marketplace

What we need:

• Job postings: title, description, location, seniority level, employment type, industry
• Job skills requirements (linked to the global skills taxonomy)
• Application tracking: applied, under review, interviewing, offered, rejected
• Easy Apply (one-click using LinkedIn profile)
• Saved jobs and job alerts
• Recruiter activity (who viewed applicant profiles)
• Job recommendations based on profile, skills, and activity

Why it matters:

• Jobs is LinkedIn's primary monetization engine
• Application tracking bridges the gap between job boards and ATS systems
• "Who viewed your profile" is a premium feature tied to job seeking behavior

6. Messaging (LinkedIn Messaging & InMail)

What we need:

• Direct messaging between 1st-degree connections
• InMail: paid messages to non-connections
• Group conversations (multi-party threads)
• Message types: text, images, files, voice messages
• Read receipts and typing indicators
• Message requests (from non-connections)
• Sponsored InMail (recruiter bulk messaging)

Why it matters:

• Messaging is the conversion funnel for recruiting and sales
• InMail is a Premium monetization feature
• Response rates and acceptance rates are tracked for senders

7. Skills & Endorsements

What we need:

• Global skills taxonomy (standardized list of 40,000+ skills)
• Users add skills to their profile (max ~50)
• Connections can endorse skills (one-click validation)
• Skill assessments (LinkedIn Skill Quizzes with badge)
• Top skills shown prominently on profile

Why it matters:

• Skills are the primary search facet for recruiters
• Endorsement counts serve as social proof
• Skill assessments differentiate verified vs. self-reported skills

8. Recommendations (Written Testimonials)

What we need:

• Request recommendations from connections
• Written recommendations (free-text) with relationship context
• Relationship type: managed directly, worked together, was a client, etc.
• Position context: "when they were Senior Engineer at Google"
• Given and received recommendations

Why it matters:

• Written recommendations carry more weight than endorsements
• They're linked to specific job positions for context
• Used heavily in recruiting and hiring decisions

9. Groups

What we need:

• Professional interest groups (public or private)
• Group membership with roles (owner, admin, member)
• Group posts and discussions
• Member approval (for private groups)
• Group rules and moderation
• Group activity notifications

Why it matters:

• Groups foster niche professional communities
• Group membership signals professional interests for ad targeting
• Group discussions generate organic content

10. Notifications & Activity

What we need:

• Profile view notifications ("X people viewed your profile")
• Connection request notifications
• Post engagement notifications (reactions, comments, mentions)
• Job alert notifications (matching saved searches)
• Birthday and work anniversary notifications
• Notification preferences (email, push, in-app, per-type)

Why it matters:

• Notifications drive return visits and engagement
• "Who viewed your profile" is a core Premium feature
• Job alerts drive application volume

Scope Definition

In Scope:

✅ User profiles with work experience, education, skills ✅ Connection network (bidirectional) with follow (unidirectional) ✅ News feed with posts, reactions, comments, reposts ✅ Company pages with employee association ✅ Job postings and applications ✅ Messaging (direct and group) ✅ Skills and endorsements ✅ Written recommendations ✅ Groups with membership and roles ✅ Notifications and profile views ✅ Certifications and projects

Out of Scope:

❌ LinkedIn Premium subscription tiers (Sales Navigator, Recruiter, Premium Career) ❌ LinkedIn Learning (courses platform) ❌ LinkedIn Ads and Campaign Manager ❌ LinkedIn Events (virtual events platform) ❌ LinkedIn Newsletters (long-form publishing) ❌ LinkedIn Sales Insights ❌ LinkedIn Talent Hub (ATS integration) ❌ Recruiter Lite vs. Recruiter Corporate licensing ❌ Verified identity badges

Key Challenges to Address

1. Bidirectional Connections vs. Unidirectional Follows

Challenge: Unlike Twitter (follow) or Facebook (friend), LinkedIn supports BOTH Solution: Separate connections table (bidirectional, requires acceptance) and follows table (unidirectional, no acceptance needed)

2. 2nd and 3rd Degree Connection Calculation

Challenge: Given 30,000 connections per user, finding 2nd-degree connections means traversing 30K × 30K = 900M edges Solution: Pre-computed connection degree via graph database or batch job. For the RDBMS model, we use indexed joins with limits

3. Multi-Position Work History

Challenge: A user can have multiple positions at the same company (promotion) or overlapping positions at different companies Solution: work_experience table with start_date, end_date, and is_current flag. Multiple rows per user-company pair allowed

4. Skills Taxonomy

Challenge: LinkedIn has 40,000+ standardized skills — users can't create arbitrary skills Solution: Separate skills master table (curated), with user_skills junction table. Endorsements reference the junction table

5. Job Application Tracking

Challenge: Applicants need to see their application status; recruiters need to see all applicants for a job Solution: job_applications table with status workflow (applied → reviewing → interviewing → offered/rejected)

6. Polymorphic Content Associations

Challenge: Reactions and comments can apply to posts, articles, and comments themselves (nested) Solution: post_reactions with FK to posts; comments with self-referencing parent_comment_id

7. Company Employee Count Accuracy

Challenge: LinkedIn shows "X employees on LinkedIn" — this must reflect CURRENT employees only Solution: Query work_experience where is_current = TRUE and company_id matches

8. Notification Fan-Out

Challenge: A post with 10,000 reactions shouldn't generate 10,000 notifications to the poster Solution: Batched notification aggregation ("Alice and 9,999 others reacted to your post")

Business Rules to Implement

Profile Rules:

1. Vanity URL (/in/username) must be globally unique
2. Headline max 220 characters
3. Summary/About max 2,600 characters
4. Maximum 50 skills per profile
5. Work experience requires at least a job title and company

Connection Rules:

1. Connections are bidirectional (mutual acceptance required)
2. Max 30,000 connections per user
3. Connection request can include a personal note (max 300 chars)
4. Withdrawn requests can be re-sent after 3 weeks
5. Blocked users cannot send connection requests

Post Rules:

1. Text posts max 3,000 characters
2. Articles (LinkedIn Publisher) have no character limit
3. Posts can have up to 20 images (carousel)
4. Polls can have up to 4 options, duration 1-14 days
5. Post visibility: Public, Connections Only

Job Rules:

1. Job postings require: title, company, location, description
2. Easy Apply requires applicant's profile data + optional resume
3. Application status visible to both applicant and recruiter
4. Jobs auto-close after the set deadline

Messaging Rules:

1. Free messaging between 1st-degree connections
2. InMail requires Premium subscription
3. Group conversations limited to 50 participants
4. Message requests from non-connections go to separate inbox

Data Model Goals

1. Normalization

• Skills as a master taxonomy table (not stored as text in user profiles)
• Companies stored once, referenced from work experience, job postings, pages
• Follow 3NF with strategic denormalization for counts (connections_count, endorsement_count)

2. Performance

• Fast profile rendering (all sections from 1 page load)
• Sub-second news feed generation
• Efficient "People You May Know" (mutual connections query)
• Fast recruiter search (skills + location + title + company filters)

3. Scalability

• 1 billion users with an average of 500 connections each
• 10 million new job postings per week
• Billions of feed impressions per day
• Millions of connection requests per hour

4. Search & Discovery

• Full-text search across profiles, jobs, companies, posts
• Faceted filtering (industry, location, company size, seniority)
• Typeahead suggestions for skills, companies, and people

Success Metrics

Our data model should support queries for:

1. Profile Page: Get all user data, work history, education, skills, and endorsement counts
2. Connection List: Get 1st-degree connections with mutual connection counts
3. People You May Know: Recommend users based on mutual connections, company, school
4. News Feed: Get posts from connections and followed entities, ranked by engagement
5. Job Search: Filter jobs by title, location, skills, company, seniority
6. Application Tracking: Get all applications for a user with status
7. Company Page: Get company details, employee count, recent posts, open jobs
8. Skill Endorsements: Get endorsement counts and endorsers per skill
9. Messaging Inbox: Get conversations sorted by most recent message
10. Profile Views: Get "Who viewed your profile" with anonymous mode support

This content is reserved for Premium Members.